package vip.liux.backend.infrastructure.jpa.point;

import jakarta.persistence.Tuple;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import vip.liux.contracts.models.point.PointsDetail;
import vip.liux.contracts.repositories.point.IPointsDetailRepository;

import java.time.LocalDateTime;
import java.util.List;

public interface JpaPointsDetailRepository extends IPointsDetailRepository, JpaRepository<PointsDetail, Long> {

    @Query("SELECT d.id as id, " +
            "      d.userId as userId, " +
            "      (d.points - COALESCE((SELECT SUM(u.usedPoints) FROM PointsUsage u WHERE u.detailId = d.id), 0)) AS remainPoints " +
            "FROM PointsDetail d " +
            "WHERE d.expiryTime IS NOT NULL " +
            "  AND d.expiryTime <= :now " +
            "  AND (d.points - COALESCE((SELECT SUM(u.usedPoints) FROM PointsUsage u WHERE u.detailId = d.id), 0)) > 0")
    List<Tuple> findExpiredPoints(LocalDateTime now);
}